package com.apes.fn.fico.expense;

import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

public interface ExpenseItemDetailsBillRepository extends CustomRepository<ExpenseItemDetailsBill, String> {

   @Query(value = "SELECT T.COMPANY_ID FROM FN_EXPENSE_ITEM_DETAILS_BILL T WHERE TRUNC(T.CREATE_DATE)  = to_date(:date,'yyyy-mm-dd') AND T.EXPENSE_ITEM_DETAILS_ID IS NULL GROUP BY T.COMPANY_ID",nativeQuery = true)
   List<String> findByCreateDate(@Param("date") String date);

   @Query(value = "SELECT T.EXPENSE_DETAILS_TYPE FROM FN_EXPENSE_ITEM_DETAILS_BILL T WHERE T.COMPANY_ID = :companyId AND TRUNC(T.CREATE_DATE)  = to_date(:date,'yyyy-mm-dd') AND T.EXPENSE_ITEM_DETAILS_ID IS NULL GROUP BY T.EXPENSE_DETAILS_TYPE",nativeQuery = true)
   List<String> findByCompany(@Param("companyId") String companyId,@Param("date") String date);

   @Query(value = "SELECT * FROM FN_EXPENSE_ITEM_DETAILS_BILL T WHERE T.COMPANY_ID = :companyId AND T.EXPENSE_DETAILS_TYPE = :expenseDetailsType AND TRUNC(T.CREATE_DATE) = to_date(:date,'yyyy-mm-dd') AND T.EXPENSE_ITEM_DETAILS_ID IS NULL",nativeQuery = true)
   List<ExpenseItemDetailsBill> findByCompanyAndExpenseDetailsType(@Param("companyId") String companyId,@Param("expenseDetailsType") String expenseDetailsType,@Param("date") String date);

   @Query(value = "SELECT * FROM fn_expense_item_details_bill T WHERE T.expense_item_details_id = :id",nativeQuery = true)
   List<Map> findByExpenseItemDetailsId(@Param("id") String id);

   @Query(value = "SELECT A.ID AID,A.COMPANY_ID ACOMPANY_ID,A.EXPENSE_DETAILS_TYPE AEXPENSE_DETAILS_TYPE,T.* " +
           "FROM FN_EXPENSE_ITEM_DETAILS A,FN_EXPENSE_ITEM_DETAILS_BILL T " +
           "WHERE A.COMPANY_ID = :companyId AND A.EXPENSE_DETAILS_TYPE = :expenseDetailsType AND A.INCOME_TYPE = 'A09'" +
           "AND T.expense_item_details_id = A.ID AND A.CREATE_DATE >= :createDateMin AND A.CREATE_DATE <= :createDate",nativeQuery = true)
   List<Map> findByExpenseItemDetailsCompanyId(@Param("companyId") String companyId, @Param("expenseDetailsType") String expenseDetailsType, @Param("createDateMin") Date createDateMin, @Param("createDate") Date createDate);


   @Query(value = "SELECT A.ID AID,A.COMPANY_ID ACOMPANY_ID,A.EXPENSE_DETAILS_TYPE AEXPENSE_DETAILS_TYPE,T.* " +
           "FROM FN_EXPENSE_ITEM_DETAILS A,FN_EXPENSE_ITEM_DETAILS_BILL T " +
           "WHERE A.COMPANY_ID = :companyId AND A.EXPENSE_DETAILS_TYPE = :expenseDetailsType AND A.INCOME_TYPE = 'A09'" +
           "AND T.expense_item_details_id = A.ID",nativeQuery = true)
   List<Map> findByExpenseItemDetailsCompanyId(@Param("companyId") String companyId, @Param("expenseDetailsType") String expenseDetailsType);


   @Query(value = "SELECT COUNT(*) FROM fn_expense_item_details_bill T WHERE T.order_id = :id ",nativeQuery = true)
   int findByOrderId(@Param("id") String id);
}
